#作者：Astilbe

import xlrd
import xlwt

def open_Students_list(file):
    stu_list_file = xlrd.open_workbook(file)
    sheet1 = stu_list_file.sheets()[0]#打开第一个sheet

    nrows = sheet1.nrows

    
    #ncols = sheet1.ncols
    for i in range(nrows - 1):
        print((i+1),sheet1.row_values(i+1))
    
    std_sum=nrows-1
    print('学生总数',std_sum)

    std_list=sheet1.col_values(0)#[1,]#读取第一列后的所有内容
    std_list.pop(0)#去除第一个"姓名"
    print(std_list)
    return std_list,std_sum
    #print('表格总列数',ncols)

def open_examRoomFlie(file):
    exam_list = xlrd.open_workbook(file)
    sheet1 = exam_list.sheets()[0]#打开第一个sheet

    nrows = sheet1.nrows
    #ncols = sheet1.ncols
    order=int(sheet1.row_values(1)[6])
    print("order=",order)
    start_direction=sheet1.row_values(1)[7]
    print("start_direction=",start_direction)
    stu_sum=0
    each_examRoom_list=[]
    for i in range(1,nrows):
        #print((i),sheet1.row_values(i))
        examRoom_name = sheet1.row_values(i)[0]
        row_value = int(sheet1.row_values(i)[1])
        col_value =int(sheet1.row_values(i)[2])
        print("%s共%d行%d列"%(examRoom_name,row_value,col_value))
        each_examRoom_list.append([examRoom_name,row_value,col_value])
        
        stu_sum+=row_value*col_value
    
    print('考场总共可容纳学生:%d位'%stu_sum)
    print(each_examRoom_list)
    
    return each_examRoom_list,order,start_direction


def makeList(exam_list,stu_list,std_num,order):
    workbook = xlwt.Workbook()#写excel
    examRoom_num = len(exam_list)
    head=0#定义头指针，每次写入一个学生，头指针向后移动一位，指针最大值为学生数目-1
    #左起

    for i in range(examRoom_num):
        worksheet = workbook.add_sheet(exam_list[i][0]+'座位表')#写考场名字位sheet
        col_value=exam_list[i][1]
        if (order == 1):
            for j in range(exam_list[i][2]):#每列
                for k in range(exam_list[i][1]):#每行
                    worksheet.write(k,j,stu_list[head])
                    #print("head",head)
                    head+=1
                    if head == std_num:
                            workbook.save('finish.xls')
                            return True

        elif(order == 2):#S型分布
            for j in range(exam_list[i][2]):#每列
                if (j%2==1):
                    for k in range((exam_list[i][1]-1),-1,-1):#双数列倒序
                        worksheet.write(k,j,stu_list[head])
                        #print("head",head)
                        head+=1
                        if head == std_num:
                                workbook.save('finish.xls')
                                return True
                else:
                    for k in range(exam_list[i][1]):#单数行顺序
                        worksheet.write(k,j,stu_list[head])
                        #print("head",head)
                        head+=1
                        if head == std_num:
                                workbook.save('finish.xls')
                                return True


       
def file_cross(file_name):
    old_file = xlrd.open_workbook(file_name)
    workbook = xlwt.Workbook()#写excel
    sheet_name_list=old_file.sheet_names()#获取所有sheet名称
    print(sheet_name_list)
    length=len(old_file.sheets())
    for i in range(length):
        worksheet=workbook.add_sheet(sheet_name_list[i])#添加工作表
        oldsheet=old_file.sheets()[i]
        nrows = oldsheet.nrows#列
        ncols = oldsheet.ncols
        print("行：",nrows,"列：",ncols)
        
        for j in range(nrows):#行
            for k in range(ncols):#列
                #print(j,"行",(ncols-1-k),"列")
                worksheet.write(j,k,oldsheet.cell(j,(ncols-1-k)).value)
                #print(oldsheet.cell(j,(ncols-1-k)).value)
        

    
    workbook.save('S型排布.xls')

    





def main():
   
    print("*********欢迎使用科大附中考场布置程序**********")
    print("***************请确保已更新学生名单**************")
    print("******************读取学生名单ing.......*************")
    std_list,std_sum=open_Students_list('考试名单.xls')
    print("**********************读取完毕**********************")

     #设置考场
    print("***********请确保考场安排文件已更新*************")
    print("******************读取考场安排ing.......************")
    each_examRoom_list,Order,start_Direction=open_examRoomFlie('考场安排.xlsx')
    print("******************开始安排考场*********************")


    finishFlag=makeList(each_examRoom_list,std_list,std_sum,Order)
    if(start_Direction=="右"):
        file_cross("finish.xls")
        print("完成*****请查看over.xls文件")
    else:
        print("完成*****请查看finish.xls文件")

    print("*******************考场布置全部结束********************")
    


    




if __name__ == '__main__':
    main()
